﻿if exists (select 1
          from sysobjects
          where  id = object_id('bd_proc_report_leave_detail')
          and type in ('P','PC'))
   drop procedure bd_proc_report_leave_detail
go


create procedure bd_proc_report_leave_detail (@date_begin DATE,@date_end DATE,@userNameOrNo VARCHAR(100),@users VARCHAR(MAX),@depts VARCHAR(MAX),@user_type VARCHAR(50),@user_company VARCHAR(200),@user_status VARCHAR(20),@is_page INT = 1,@page_size INT = 50,@page_no INT = 1,@row_count INT = 0 OUTPUT) as
BEGIN
  SET NOCOUNT ON;
--工号、姓名、员工类型、所属公司、工作状态

  CREATE TABLE #result_detail
  (
    user_serial BIGINT
    , bill_id VARCHAR(100)    --请假ID
		, jsjg DECIMAL(18,2) --请假时间量
  )

	CREATE TABLE #result
  (
    user_serial BIGINT
    , user_no VARCHAR(20)
    , user_name VARCHAR(200) COLLATE Chinese_PRC_CI_AS
    , dept_id BIGINT
    , dept_name VARCHAR(500) COLLATE Chinese_PRC_CI_AS
    , card_no VARCHAR(50) COLLATE Chinese_PRC_CI_AS --员工卡号
    , card_manhour VARCHAR(50) COLLATE Chinese_PRC_CI_AS  --员工属性工时制
    , company VARCHAR(200) COLLATE Chinese_PRC_CI_AS    --员工属性所属公司
    , user_type VARCHAR(50) COLLATE Chinese_PRC_CI_AS   --员工属性员工类型
    , user_status VARCHAR(50) COLLATE Chinese_PRC_CI_AS  --员工属性工作状态

    , bill_no VARCHAR(100)    --请假单号
    , applicant_no VARCHAR(50) --申请人工号
    , applicant_name VARCHAR(100)  --申请人姓名

    , date_begin DATE     --开始日期
    , time_begin VARCHAR(10)   --开始时间 8:00

    , date_end  DATE      --结束日期
    , time_end VARCHAR(10)     --结束时间 16:00

    , create_time DATETIME
		, status_name varchar(50) --状态名称
		,total_hours DECIMAL(18,2) --请假时长小时数
		,leave_type_name varchar(50)
  )

--	流程编号：请假单号
--	申请人工号：请假申请人工号
--	申请人姓名：请假申请人姓名
--	请假类型：假类维护数据
--	申请开始日期：请假申请开始日期
--	申请开始时间：请假申请开始时间
--	申请结束日期：请假申请结束日期
--	申请结束时间：请假申请结束时间

 DECLARE @sql VARCHAR(MAX) = ''

  SELECT @sql = '
    INSERT INTO #result_detail (user_serial, bill_id,jsjg)
		select k.user_serial,k.order_id,k.jsjg from lr_kqingj k
		INNER JOIN bd_view_user_all u ON u.user_serial = k.user_serial
		WHERE convert(varchar(10),k.kssj,120)>= '''+convert(varchar(10),@date_begin,120)
		+''' and convert(varchar(10),k.jssj,120) <='''+ convert(varchar(10),@date_end,120)+''''


  IF(ISNULL(@userNameOrNo, '') <> '' AND ISNULL(@users, '') = '')
  BEGIN
  	SELECT @sql += ' AND u.user_no LIKE ''%' + @userNameOrNo + '%'' OR u.user_lname LIKE ''%' + @userNameOrNo + '%'''
  END

  IF(ISNULL(@users, '') <> '')
  BEGIN
  	SELECT @sql += ' AND u.user_serial IN(' + @users + ')'
  END

  IF(ISNULL(@depts, '') <> '')
  BEGIN
  	SELECT @sql += ' AND u.user_dep IN(' + @depts + ')'
  END

  IF(ISNULL(@user_type, '') <> '')
  BEGIN
  	SELECT @sql += ' AND u.user_sny_lx = ''' + @user_type + ''''
  END

  IF(ISNULL(@user_company, '') <> '')
  BEGIN
  	SELECT @sql += ' AND u.user_company LIKE ''%' + @user_company + '%'''
  END

  IF(ISNULL(@user_status, '') <> '')
  BEGIN
  	SELECT @sql += ' AND u.user_sny_type = ''' + @user_status + ''''
  END
	print @sql

  EXEC(@sql)

--分组求和放入#result

	INSERT INTO #result (user_serial, user_no, user_name, dept_id, dept_name, card_no, card_manhour, company, user_type, user_status,
	bill_no, applicant_no, applicant_name, date_begin, time_begin, date_end, time_end, create_time,status_name,total_hours,leave_type_name)
	select
		x.user_serial, u.user_no, u.user_lname, u.user_dep, u.dep_name, u.card_number, u.man_hour_system, u.user_company, u.user_sny_lx, u.user_sny_type,
		o.order_no, cu.user_no, cu.user_lname, o.start_date, o.begin_time, o.end_date, o.end_time, o.create_time,
		CASE o.status WHEN 1 THEN '审批中' WHEN 2 THEN '驳回' WHEN 3 THEN '通过' WHEN 4 THEN '自动审批' WHEN 5 THEN '取消' ELSE '待审批' END,
		x.total_minute/60.0,q.mc
	from bd_leave_order o
	INNER JOIN(
		select  user_serial,bill_id,sum(jsjg) as total_minute  from #result_detail GROUP BY user_serial,bill_id
	) x on x.bill_id = o.order_id
	INNER JOIN bd_view_user_all u ON u.user_serial = x.user_serial
  INNER JOIN (SELECT user_serial, user_no, user_lname FROM bd_view_user_all) cu ON cu.user_serial = o.creater_id
	INNER JOIN kt_qingj q on q.bh = o.leave_id
	where o.is_del = 0

  SELECT @row_count = COUNT(1) FROM #result

  IF(ISNULL(@is_page, 0) = 0)
  BEGIN
  	SELECT
			user_serial, user_no, user_name, dept_id, dept_name, card_no, card_manhour, company, user_type, user_status, bill_no, applicant_no, applicant_name,
			convert(varchar(10),date_begin,120) as date_begin,
			time_begin,
			convert(varchar(10),date_end,120) as date_end,
			time_end,
			convert(varchar(19),create_time,120) as create_time,
			status_name, total_hours,leave_type_name
		FROM #result ORDER BY create_time DESC
  END ELSE
  BEGIN
    ;WITH result AS (
      SELECT *,row_no = ROW_NUMBER() OVER (ORDER BY create_time DESC) FROM #result
    )
    SELECT user_serial, user_no, user_name, dept_id, dept_name, card_no, card_manhour, company, user_type, user_status, bill_no, applicant_no, applicant_name,
		convert(varchar(10),date_begin,120) as date_begin,
		time_begin,
		convert(varchar(10),date_end,120) as date_end,
		time_end,
		convert(varchar(19),create_time,120) as create_time,
		status_name, total_hours,leave_type_name, row_no
    FROM result
    WHERE row_no BETWEEN (@page_no - 1) * @page_size + 1 AND @page_no * @page_size
  END

  DROP TABLE #result

  RETURN @row_count

  SET NOCOUNT OFF;
END
go
